const express = require('express');
const router = express.Router();
const { _Op } = require('sequelize');
const Field = require('../models/Field');
const _Certificate = require('../models/Certificate');
const logger = require('../utils/logger');
const sequelize = require('../config/database');

// 获取字段列表
router.get('/', async (req, res) => {
  try {
    const { page = 1, limit = 10, name = '' } = req.query;
    const offset = (page - 1) * limit;

    const where = {};
    if (name) {
      where.name = {
        [Op.like]: `%${name}%`
      };
    }

    const { count, rows } = await Field.findAndCountAll({
      where,
      offset,
      limit: parseInt(limit),
      order: [['id', 'ASC']]
    });

    res.json({
      code: 200,
      data: {
        total: count,
        items: rows
      }
    });
  } catch (error) {
    logger.error('Get fields error:', error);
    res.status(500).json({
      code: 500,
      message: '获取字段列表失败'
    });
  }
});

// 创建字段
router.post('/', async (req, res) => {
  try {
    const { name, field_name, description, required } = req.body;
    logger.info('Creating field with data:', { name, field_name, description, required });

    // 验证必填字段
    if (!name || !field_name) {
      logger.warn('Missing required fields:', { name, field_name });
      return res.status(400).json({
        code: 400,
        message: '字段名称和字段标识不能为空'
      });
    }
    
    // 获取下一个可用的 id
    const nextId = await Field.getNextId();
    logger.info('Creating field with ID:', nextId);
    
    // 开启事务
    const t = await sequelize.transaction();
    logger.info('Transaction started');
    
    try {
      const field = await Field.create({
        id: nextId,
        name,
        field_name,
        description: description || '',
        required: required || false
      }, { transaction: t });

      await t.commit();
      logger.info('Field created successfully:', field.toJSON());

      res.json({
        code: 200,
        data: field
      });
    } catch (error) {
      logger.error('Error during field creation:', error);
      await t.rollback();
      logger.info('Transaction rolled back');
      throw error;
    }
  } catch (error) {
    logger.error('Create field error:', error);
    
    // 处理特定的错误类型
    if (error.name === 'SequelizeUniqueConstraintError') {
      const errorPath = error.errors[0]?.path;
      const errorMessage = errorPath === 'field_name' ? '字段标识已存在' : '字段名称已存在';
      logger.warn('Unique constraint violation:', { path: errorPath, message: errorMessage });
      return res.status(400).json({
        code: 400,
        message: errorMessage
      });
    }
    
    res.status(500).json({
      code: 500,
      message: error.message || '创建字段失败'
    });
  }
});

// 更新字段
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, field_name, description, required } = req.body;

    const field = await Field.findByPk(id);
    if (!field) {
      return res.status(404).json({
        code: 404,
        message: '字段不存在'
      });
    }

    // 验证必填字段
    if (!name || !field_name) {
      return res.status(400).json({
        code: 400,
        message: '字段名称和字段标识不能为空'
      });
    }

    await field.update({
      name,
      field_name,
      description,
      required: required || false
    });

    res.json({
      code: 200,
      data: field
    });
  } catch (error) {
    logger.error('Update field error:', error);
    
    // 处理特定的错误类型
    if (error.name === 'SequelizeUniqueConstraintError') {
      return res.status(400).json({
        code: 400,
        message: error.errors[0]?.path === 'field_name' ? '字段标识已存在' : '字段名称已存在'
      });
    }
    
    res.status(500).json({
      code: 500,
      message: error.message || '更新字段失败'
    });
  }
});

// 删除字段
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    logger.info('开始删除字段，ID:', id);

    // 查找字段
    const field = await Field.findByPk(id);
    if (!field) {
      logger.warn('字段不存在:', id);
      return res.status(404).json({
        code: 404,
        message: '字段不存在'
      });
    }
    logger.info('找到字段:', field.toJSON());

    // 检查是否是系统字段
    if (field.field_name === 'cert_number' || field.id === 1) {
      logger.warn('尝试删除系统字段:', field.toJSON());
      return res.status(403).json({
        code: 403,
        message: '系统字段不允许删除'
      });
    }

    // 检查字段名称是否存在
    if (!field.field_name) {
      logger.warn('字段标识为空:', field.toJSON());
      return res.status(400).json({
        code: 400,
        message: '字段标识不能为空'
      });
    }

    // 检查是否有证书正在使用该字段
    const [results] = await sequelize.query(
      `SELECT COUNT(*) as count, GROUP_CONCAT(sequence_number) as cert_numbers 
       FROM certificates 
       WHERE JSON_SEARCH(fields, 'one', ?) IS NOT NULL`,
      {
        replacements: [field.field_name],
        type: sequelize.QueryTypes.SELECT
      }
    );

    const { count, cert_numbers } = results;
    logger.info('字段使用检查结果:', { count, cert_numbers });
    
    if (count > 0) {
      logger.warn(`字段 ${id} 被以下证书使用: ${cert_numbers}`);
      return res.status(403).json({
        code: 403,
        message: `该字段已被${count}个证书使用（证书序号：${cert_numbers}），不能删除。`
      });
    }

    // 开启事务
    const t = await sequelize.transaction();

    try {
      // 1. 删除字段
      await field.destroy({ transaction: t });

      // 2. 更新所有大于当前 ID 的字段的 ID（减1）
      await sequelize.query(
        'UPDATE fields SET id = id - 1 WHERE id > :deletedId',
        {
          replacements: { deletedId: id },
          type: sequelize.QueryTypes.UPDATE,
          transaction: t
        }
      );

      // 提交事务
      await t.commit();
      logger.info('字段删除成功:', id);

      res.json({
        code: 200,
        message: '删除成功'
      });
    } catch (error) {
      // 如果出错，回滚事务
      await t.rollback();
      logger.error('删除字段事务失败:', error);
      throw error;
    }
  } catch (error) {
    logger.error('删除字段失败:', error);
    res.status(500).json({
      code: 500,
      message: '删除字段失败'
    });
  }
});

module.exports = router; 